import os
from io import BytesIO
import pandas as pd
import re
from sqlalchemy import desc
from models import EmployeeModel, RoleModel, Leave_vacationModel
from exts import db
from flask import jsonify, flash, send_file, request
from utils import Info
from datetime import datetime
from services import daypaper_service


def show_all():
    employees = EmployeeModel.query.all()  # 获取所有员工
    employee_list = []
    stu_employee_list = []
    tea_employee_list = []
    for employee in employees:
        # 根据 employee.role 查询对应的 RoleModel 实例
        role = RoleModel.query.get(employee.role)  # 根据角色 ID 获取角色对象

        # 获取角色描述，处理角色未定义的情况
        role_description = role.description if role else "未定义的角色"
        if role_description == "职工":
            tea_employee_list.append({
                "id": employee.id,
                "name": employee.name,
                "email": employee.email,
                "role": role_description  # 使用角色描述
            })
        else:
            stu_employee_list.append({
                "id": employee.id,
                "name": employee.name,
                "email": employee.email,
                "role": role_description  # 使用角色描述
            })
    employee_list = tea_employee_list + stu_employee_list
    return True, Info(msg="查询成功", data=employee_list)


def query(**kwargs):
    # 获取字典数据，例如 {'name': '张', 'id': None, 'email': '123@qq.com'}
    # 传入任意查询数据库
    query = EmployeeModel.query  # 初始化查询对象

    for key, value in kwargs.items():
        if value:  # 仅当值不为 None 或空字符串时才进行查询
            # 检查是否需要进行模糊查询
            if key in ['name', 'email']:  # 设置模糊查询的字段
                # 使用 SQLAlchemy 的 like 进行模糊查询
                query = query.filter(getattr(EmployeeModel, key).like(f'%{value}%'))
            else:
                # 对于其他字段使用 filter_by 进行精确匹配
                query = query.filter_by(**{key: value})

    employees = query.all()  # 获取所有匹配的员工

    if employees:
        # 构建返回的数据列表
        employee_data_list = []
        for employee in employees:
            employee_data = {
                "id": employee.id,
                "name": employee.name,
                "email": employee.email,
                "role": employee.identity.description if employee.identity else None  # 考虑角色可能为 None 的情况
            }
            employee_data_list.append(employee_data)
        return employee_data_list  # 返回所有匹配的员工数据
    else:
        return []  # 返回空列表而不是 False


def add(name, email, role_description):
    if query(email=email):  # 查询员工表中是否已存在邮
        return False, Info(msg="邮箱已存在, 无法添加")
        # return jsonify({"error": "邮箱已存在, 无法添加"}), 400  # 返回 400 Bad Request 状态
    role = RoleModel.query.filter_by(description=role_description).first()

    if role is None:
        return False, Info(msg=f'未定义的角色: {role_description}，添加失败')
    employee = EmployeeModel.query.filter_by(name=name).all()
    if len(employee):
        return False, Info(msg=f'添加失败，员工{name}存在重名问题，建议修改为{name}{len(employee) + 1}')
    employee = EmployeeModel(name=name, email=email, role=role.identity)

    try:
        # 添加到数据库
        db.session.add(employee)
        db.session.commit()
        return True, Info(msg="员工添加成功！")
    except Exception as e:
        db.session.rollback()  # 回滚数据库会话
        return False, Info(msg=str(e))


def is_valid_email(email):
    """ 验证邮箱格式 """
    if pd.isnull(email):  # 处理空值
        return False
    email_pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    return re.match(email_pattern, email) is not None


def uploadExcel(file):
    # 确定文件格式并选择合适的引擎
    file_extension = os.path.splitext(file.filename)[-1].lower()

    if file_extension == '.xls':
        engine = 'xlrd'
    elif file_extension == '.xlsx':
        engine = 'openpyxl'
    else:
        return False, Info(msg="不支持的文件格式。请上传 .xls 或 .xlsx 文件。")

    # 读取 Excel 文件
    try:
        df = pd.read_excel(file, engine=engine)
    except Exception as e:
        return False, Info(msg=f"读取文件失败: {e}")

    error_messages = []
    success_messages = []

    for index, row in df.iterrows():
        # 使用 strip() 函数去除两边的空格
        name = row["姓名"].strip() if pd.notnull(row["姓名"]) else ""
        email = row["邮箱"].strip() if pd.notnull(row["邮箱"]) else ""
        role = row["角色"].strip() if pd.notnull(row["角色"]) else ""

        # 验证数据
        if not email or not is_valid_email(email):
            error_messages.append(f"添加失败，员工 {name} 的邮箱格式不正确。")
            continue

        if not role:
            error_messages.append(f"添加失败，员工 {name} 的角色为空。")
            continue

        existing_employee = EmployeeModel.query.filter_by(email=email).first()
        if existing_employee:
            if existing_employee.name == name:
                error_messages.append(f"添加失败，员工 {name} 已存在！")
            else:
                error_messages.append(f"添加失败，邮箱 {email} 已被其他员工 '{existing_employee.name}' 使用！")
            continue

        employee_with_same_name = EmployeeModel.query.filter_by(name=name).all()
        if len(employee_with_same_name):
            error_messages.append(f'添加失败，员工 {name} 存在重名问题，建议修改为 {name}{len(employee_with_same_name) + 1}')
            continue

        role_record = RoleModel.query.filter_by(description=role).first()
        if role_record is None:
            error_messages.append(f"添加失败，员工 {name} 的角色无效。")
            continue

        # 插入新员工数据
        new_employee = EmployeeModel(name=name, email=email, role=role_record.identity)
        db.session.add(new_employee)
        success_messages.append(f"添加成功，员工 {name} 添加成功。")

    # 提交数据库事务
    if error_messages:
        error_messages.insert(0, "导入失败，请修改后重新导入。")
        db.session.rollback()
        return False, Info('导入失败', error_messages)
    else:
        db.session.commit()
        return True, Info("导入成功！", success_messages)


def edit(employee_id, name, email, role_description):
    # 查找要更新的员工
    employee = EmployeeModel.query.get(employee_id)
    if not employee:
        return False, Info(msg="员工未找到。")
        # return jsonify({"error": "员工未找到"}), 404

    # 验证输入数据
    if not name or not email:
        return False, Info(msg="姓名、邮箱是必需的。")
        # return jsonify({"error": "姓名、邮箱是必需的"}), 400
        # 允许角色为 0，进行额外检查
    if role_description is None or (isinstance(role_description, str) and role_description.strip() == ""):
        return jsonify({"error": "角色是必需的"}), 400
    # 检查邮箱是否已被其他员工使用
    existing_employee = EmployeeModel.query.filter(EmployeeModel.email == email,
                                                   EmployeeModel.id != employee_id).first()
    if existing_employee:
        return False, Info(msg=f"邮箱 '{email}' 已被其他员工 '{existing_employee.name}' 使用！")
    duplicate_employee = EmployeeModel.query.filter(EmployeeModel.name == name, EmployeeModel.id != employee_id).all()
    # employee = EmployeeModel.query.filter(name=name, EmployeeMode).all()
    if len(duplicate_employee) == 1:
        return False, Info(msg=f'修改失败，员工{name}存在重名问题，建议修改为{name}{len(duplicate_employee) + 1}')

        # return jsonify({"error": f"邮箱 '{email}' 已被其他员工 '{existing_employee.name}' 使用！"}), 400

    # 更新员工信息
    employee.name = name
    employee.email = email
    # if role == "学生":
    #     role = 0
    # elif role == "老师":
    #     role = 1
    # employee.role = role
    role = RoleModel.query.filter_by(description=role_description).first()

    if role is None:
        # raise ValueError(f"未定义的角色: {role_description}")
        return False, Info(msg=f"未定义的角色: {role_description}，修改失败")
        # return jsonify({"error": f"未定义的角色: {role_description}，添加失败"}), 400
    employee.role = role.identity

    # 提交更改
    try:
        db.session.commit()
        return True, Info(msg="员工信息更新成功")
        # return jsonify({"message": "员工信息更新成功"}), 200
    except Exception as e:
        db.session.rollback()  # 回滚事务
        return False, Info(msg=f"更新失败: {e}")
        # return jsonify({"error": f"更新失败: {e}"}), 500


def delete(id):
    # 查询学生是否存在
    employee_data = query(id=id)
    if employee_data:
        id = employee_data[0]["id"]
        employee = EmployeeModel.query.get(id)
        try:
            # 1.删除员工的所有日报记录
            daypapers = employee.daypapers  # 双重映射
            for daypaper in daypapers:
                db.session.delete(daypaper)
            leaves = employee.leave_vacations
            for leave in leaves:
                db.session.delete(leave)

            db.session.commit()

            # 2.删除该员工
            db.session.delete(employee)
            db.session.commit()
            return True, Info(msg="员工信息删除成功。")
            # return jsonify({"message": "员工信息删除成功"}), 200

        except Exception as e:
            db.session.rollback()
            # print(f"删除时发生错误,error:{e}")
            return False, Info(msg="删除失败。")
            # return jsonify({"error": "删除失败"}), 200

    else:
        return False, Info(msg="员工信息未找到。")
        # return jsonify({"error": "员工信息未找到"}), 200


def bulk_delete(ids_to_delete):
    success_messages = []
    error_messages = []

    # 先查询所有要删除的员工
    employees_to_delete = EmployeeModel.query.filter(EmployeeModel.id.in_(ids_to_delete)).all()
    existing_ids = {employee.id for employee in employees_to_delete}
    # 处理删除
    for employee_id in ids_to_delete:
        if employee_id not in existing_ids:
            error_messages.append(f"员工 ID {employee_id} 未找到")
            continue

        # 如果存在，进行删除
        employee = EmployeeModel.query.get(employee_id)
        if employee:
            try:
                # 1. 删除员工的所有日报记录
                daypapers = employee.daypapers  # 假设有双向关系
                for daypaper in daypapers:
                    db.session.delete(daypaper)
                leaves = employee.leave_vacations
                for leave in leaves:
                    db.session.delete(leave)
                db.session.commit()  # 提交以删除日报记录

                # 2. 删除该员工
                db.session.delete(employee)
                db.session.commit()  # 提交以删除员工

                success_messages.append(f"员工{employee.name} 删除成功")
            except Exception as e:
                db.session.rollback()  # 如果出错则回滚事务
                error_messages.append(f"员工{employee.name} 删除失败: {str(e)}")
                # 1

    data = {
        "success": success_messages,
        "error": error_messages
    }
    return True, Info("删除成功", data)


def batch_export():
    try:
        # 从请求中获取员工 ID 列表
        ids = request.json.get('ids', [])  # 获取 ID 列表

        # 根据 ID 查询员工
        employees = EmployeeModel.query.filter(EmployeeModel.id.in_(ids)).all()  # 查询选中的员工

        # 查询所有角色并构建角色映射字典
        roles = RoleModel.query.all()
        role_mapping = {role.identity: role.description for role in roles}

        # 创建一个 DataFrame
        data = {
            "ID": [employee.id for employee in employees],
            "姓名": [employee.name for employee in employees],
            "邮箱": [employee.email for employee in employees],
            "角色": [
                role_mapping.get(employee.role, '未知角色')  # 使用字典映射转换角色
                for employee in employees
            ],
        }

        df = pd.DataFrame(data)

        # 创建一个字节流
        output = BytesIO()
        # 将 DataFrame 导出为 Excel 文件
        df.to_excel(output, index=False, sheet_name='员工信息')

        # 将流指向开头
        output.seek(0)

        # 创建响应，返回 Excel 文件
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name='employees.xlsx'
        )
    except Exception as e:
        return jsonify({
            "status": "error",
            "msg": f"导出失败: {str(e)}",
            "data": None
        }), 500  # 返回 500 错误代码


def leave_vacation(employee_id, status, reason, start_date, end_date):
    if start_date > end_date:
        return False, Info(msg="起始日期晚于结束日期，请假失败")
    leave = Leave_vacationModel.query.filter_by(em_id=employee_id).order_by(desc(Leave_vacationModel.end_date)).first()
    if leave:
        leave_start_date = str(leave.start_date)
        leave_end_date = str(leave.end_date)
        if start_date == leave_start_date and end_date == leave_end_date:
            return False, Info(msg=f"该时间段内，员工{leave.employee.name}已经请过假，请假失败")
    leave_vacation = Leave_vacationModel(em_id=employee_id, status=status, reason=reason, start_date=start_date,end_date=end_date)
    print(employee_id, status, reason, start_date, end_date)
    db.session.add(leave_vacation)
    db.session.commit()

    # 若起始日期小于当天，直接修改日报表
    today = datetime.today().date()
    start_date = datetime.strptime(start_date, "%Y-%m-%d").date()
    end_date = datetime.strptime(end_date, "%Y-%m-%d").date()

    if start_date < today:
        daypaper_service.update_vacation(employee_id, status, start_date, end_date)

    return True, Info(msg="请假成功")


def bulk_leave(employees_id, status, reason, start_date, end_date):
    success_messages = []
    error_messages = []
    employees_to_leave = EmployeeModel.query.filter(EmployeeModel.id.in_(employees_id)).all()
    existing_ids = {employee.id for employee in employees_to_leave}

    today = datetime.today().date()
    start_date = datetime.strptime(start_date, "%Y-%m-%d").date()
    end_date = datetime.strptime(end_date, "%Y-%m-%d").date()

    for employee_id in employees_id:
        if employee_id not in existing_ids:
            error_messages.append(f"员工 ID {employee_id} 未找到")
            continue
        employee = EmployeeModel.query.get(employee_id)
        if employee:
            if start_date > end_date:
                error_messages.append("起始日期晚于结束日期，请假失败")
            leave = Leave_vacationModel.query.filter_by(em_id=employee_id).order_by(
                desc(Leave_vacationModel.end_date)).first()
            if leave:
                leave_start_date = str(leave.start_date)
                leave_end_date = str(leave.end_date)
                if start_date == leave_start_date and end_date == leave_end_date:
                    error_messages.append(f"该时间段内，员工{leave.employee.name}已经请过假，请假失败")
                    continue
            leave_vacation = Leave_vacationModel(em_id=employee_id, status=status, reason=reason, start_date=start_date,
                                                 end_date=end_date)
            db.session.add(leave_vacation)
            success_messages.append(f"员工{employee.name}休假成功")
            print(employee_id, status, reason, start_date, end_date)

        # 若起始日期小于当天，直接修改日报表
        if start_date < today:
            daypaper_service.update_vacation(employee_id, status, start_date, end_date)

    if error_messages:

        error_messages = list(set(error_messages))
        error_messages.insert(0, "请假失败，请查看后重新操作。")
        return False, Info("休假失败。", error_messages)
    else:
        db.session.commit()
        return True, Info("休假成功。", success_messages)
